﻿#!/usr/bin/evn perl -w
use strict;

#打开文件
if (! open FILE , '<', 'dst/result_txt_clean.txt') {
	die "file read error";
}

=pod
my $pid = fork();
if (!defined($pid)) {
	print "resources not avilable.\n";
}elsif ($pid == 0) {
	print "child:$pid\n";
} else {
	print "parent:$pid\n";
}
=cut

use DBI;

#初始化db
my $driver = "DBI:mysql";
my $database="iac_db"; 
my $user="root"; 
my $host="192.168.56.2"; 
my $passwd="root"; 

my $dbh = DBI->connect("$driver:database=$database;host=$host;user=$user;password=$passwd:mysql_local_infile=1") or die "Can't connect: " . DBI->errstr; 

=pod

create table co_base_address_list
(
   address_id           varchar(18) not null comment '自动生成序号',
   address_level        varchar(1),
   paddress_id          varchar(20),
   address_number       varchar(18) not null comment '国标18位，中国为18个零',
   address_name         varchar(100) comment '国标，地址名称',
   valid_flag           char(1) comment '1有效0无效',
   content              varchar(255),
   insert_operator      varchar(20) comment 'web应用备留，目前初始为admin',
   insert_date          date comment '插入时取系统当前时间',
   update_operator      varchar(20) comment 'web应用备留，修改时操作人员，目前默认admin',
   update_date          date comment '修改时取系统当前时间',
   primary key (address_id)
);

create table co_base_address_lable
(
   address_id           varchar(18) not null comment '自动生成序号',
   longitude            float comment '来源地理信息',
   latitude             float comment '来源地理信息',
   precise              char(1) comment '来源地理信息',
   confidence           int comment '来源地理信息',
   llevel               varchar(20) comment '来源地理信息',
   proc_flag            int comment '0待处理1处理完成',
   valid_flag           char(1) comment '1有效0无效',
   content              varchar(255),
   insert_operator      varchar(20) comment 'web应用备留，目前初始为admin',
   insert_date          date comment '插入时取系统当前时间',
   update_operator      varchar(20) comment 'web应用备留，修改时操作人员，目前默认admin',
   update_date          date comment '修改时取系统当前时间',
   primary key (address_id)
);

my $sth = $dbh->prepare("INSERT INTO co_base_address_list(address_id, address_level, paddress_id, address_number, address_name)"
$sth->execute( $id, $lvl, "pid", $id, $name );
=cut

#打开输出文件
if (! open DB_FILE , '>', 'dst/co_base_address_list.txt') {
	die "file read error";
}

#获取数据
my $cnt;
while (<FILE>) {
	chomp;
	my ($id, $lvl, $name, $pid, $plvl, $pname) = split /:/;
	print DB_FILE "$id:$lvl:$pid:$id:$name\n";

	$cnt += 1;
}

print $cnt;
print "\n";

#导入db
my $sql = <<EOF;
LOAD DATA LOCAL INFILE "D:////zhangle////document////git////perl-test////src////gis////dst////co_base_address_list.txt" 
INTO TABLE co_base_address_list 
character set utf8
FIELDS TERMINATED BY ':' 
LINES TERMINATED BY '\\n' 
(address_id, address_level, paddress_id, address_number, address_name)
;
EOF
#这里有一个小技巧，路径需要4个////才好使
#打印sql
print $sql;

#开始入库
my $sth = $dbh->prepare($sql);
$sth->execute;
my $numRows = $sth->rows;
print $numRows;
$sth->finish;
